package com.geline.luckysheet.util;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.StringTokenizer;

/**
 * 参考：https://blog.csdn.net/ol_m_lo/article/details/120766043
 * 来源：https://gitee.com/lmchh/lmc-tools
 * @ClassName: LuckySheetExcelUtil
 * @author: Leemon
 * @Description: TODO
 * @date: 2021/6/9 18:06
 * @version: 1.0
 */
@Slf4j
public class LuckySheetExcelUtil {

    private static final int LUCKY_ROW = 20;//luckySheet默認行高
    private static final int LUCKY_COL = 75;//luckySheet默認列寬
    private static final int ROW_TIMES = 17;//luckySheet行高於Excel行高
    private static final int ROW_MUL = LUCKY_ROW - ROW_TIMES;//luckySheet於Excel行高差距
    private static final int COL_TIMES = 45;//luckySheet列寬於Excel比例

    /***
     * 基于POI解析 从0开始导出xlsx文件，不是基于模板
     * @param newFileName 保存的文件名
     * @param excelData luckysheet 表格数据
     */
    public static String exportLuckySheetXlsxByPOI(String newFileName, String excelData) {
        //去除luckysheet中 &#xA 的换行
        excelData = excelData.replace("&#xA;", "\\r\\n");
        //获取luckysheet数据  sheet:[{name:sheet1, ...},{}, ...]
        JSONArray jsonArray = JSONUtil.parseArray(excelData);
        //创建操作Excel的XSSFWorkbook对象
        XSSFWorkbook excel = new XSSFWorkbook();
        String filePath = System.getProperty("user.dir")+"/" + newFileName;
        for (int sheetIndex = 0; sheetIndex < jsonArray.size(); sheetIndex++) {
            //获取sheet
            JSONObject jsonObject = (JSONObject) jsonArray.get(sheetIndex);
            JSONArray celldataObjectList = jsonObject.getJSONArray("celldata");//获取所有单元格（坐标，内容，字体类型，字体大小...）
            JSONArray rowObjectList = jsonObject.getJSONArray("visibledatarow");
            JSONArray colObjectList = jsonObject.getJSONArray("visibledatacolumn");
            //JSONArray dataObjectList = jsonObject.getJSONArray("data");//获取所有单元格,与celldata类似（坐标，内容，字体类型，字体大小...）
            //JSONObject mergeObject = jsonObject.getJSONObject("config").getJSONObject("merge");//合并单元格
            JSONObject columnlenObject = jsonObject.getJSONObject("config").getJSONObject("columnlen");//表格列宽
            JSONObject rowlenObject = jsonObject.getJSONObject("config").getJSONObject("rowlen");//表格行高
            JSONArray borderInfoObjectList = jsonObject.getJSONObject("config").getJSONArray("borderInfo");//边框样式

            //参考：https://blog.csdn.net/jdtugfcg/article/details/84100315
            //XSSFCellStyle cellStyle = excel.createCellStyle();
            //创建XSSFSheet对象
            XSSFSheet sheet = excel.createSheet(jsonObject.getStr("name"));

            //我们都知道excel是表格，即由一行一行组成的，那么这一行在java类中就是一个XSSFRow对象，我们通过XSSFSheet对象就可以创建XSSFRow对象
            //如：创建表格中的第一行（我们常用来做标题的行)  XSSFRow firstRow = sheet.createRow(0); 注意下标从0开始
            //根据luckysheet创建行列
            //创建行和列
            if (rowObjectList != null && rowObjectList.size() > 0) {
                for (int i = 0; i < rowObjectList.size(); i++) {
                    XSSFRow row = sheet.createRow(i);//创建行
                    try {
                        row.setHeightInPoints(Float.parseFloat(rowlenObject.get(i) + "") - ROW_MUL);//行高px值
                    } catch (Exception e) {
                        row.setHeightInPoints(ROW_TIMES);//默认行高
                    }
                    if (colObjectList != null && colObjectList.size() > 0) {
                        for (int j = 0; j < colObjectList.size(); j++) {
                            if (columnlenObject != null && columnlenObject.getInt(j + "") != null) {
                                sheet.setColumnWidth(j, columnlenObject.getInt(j + "") * COL_TIMES);//列宽px值
                            }else {
                                sheet.setColumnWidth(j, LUCKY_COL * COL_TIMES);
                            }
                            row.createCell(j);//创建列
                        }
                    }
                }
            }

            //设置值,样式
            setCellValue(celldataObjectList, borderInfoObjectList, sheet, excel);

            try {
                OutputStream out = new FileOutputStream(filePath);
                excel.write(out);
                out.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        /*for (int sheetIndex = 0; sheetIndex < jsonArray.size(); sheetIndex++) {
            JSONObject jsonObject = (JSONObject) jsonArray.get(sheetIndex);//获取sheet
            JSONArray chartList = jsonObject.getJSONArray("chart");//echart圖表
            //插入Echart圖片
            if (chartList != null) {
                try {
                    JSONObject echartImg = JSONUtil.parseObj(echartImgStr);//String echartImgStr,
                    setEchart(chartList, jsonObject, sheetIndex, echartImg, newFileDir+newFileName);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }*/
        return filePath;
    }

    /**
     * 合并单元格与填充单元格颜色
     * @param jsonObjectValue
     * @param sheet
     * @param style
     */
    private static void setMergeAndColorByObject(JSONObject jsonObjectValue, XSSFSheet sheet, XSSFCellStyle style) {
        JSONObject mergeObject = (JSONObject) jsonObjectValue.get("mc");
        //合併單元格
        if (mergeObject != null) {
            int r = mergeObject.getInt("r");
            int c = mergeObject.getInt("c");
            if ((mergeObject.get("rs") != null && (mergeObject.get("cs") != null))) {
                int rs = mergeObject.getInt("rs");
                int cs = mergeObject.getInt("cs");
                CellRangeAddress region = new CellRangeAddress(r, r + rs - 1, (short) (c), (short) (c + cs - 1));
                sheet.addMergedRegion(region);
            }
        }
        //填充顏色
        if (jsonObjectValue.getStr("bg") != null) {
            String bgColor = jsonObjectValue.getStr("bg");
            if(bgColor.indexOf("rgb")>=0){
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    //设置填充方案
                style.setFillForegroundColor(new XSSFColor(getColor(bgColor)));  //设置填充颜色
            }else {
                int bg = Integer.parseInt(bgColor.replace("#", ""), 16);
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    //设置填充方案
                style.setFillForegroundColor(new XSSFColor(new Color(bg)));  //设置填充颜色
            }
        }
    }

    /**
     * 解析获取颜色
     * @param colorStr  10,255,12
     * @return
     */
    private static Color getColor(String colorStr){
        colorStr = colorStr.replace("rgb", "").replace("(", "").replace(")", "");
        StringTokenizer var2 = new StringTokenizer(colorStr, ",");
        if (var2.countTokens() == 3) {
            int var3 = 0;
            int var4 = 0;
            int var5 = 0;
            int var6;
            for(var6 = 0; var2.hasMoreTokens(); ++var3) {
                String var7 = (String)var2.nextElement();
                switch(var3) {
                    case 0:
                        if (!var7.trim().equals("")) {
                            var4 = new Integer(var7.trim());
                        }
                        break;
                    case 1:
                        if (!var7.trim().equals("")) {
                            var5 = new Integer(var7.trim());
                        }
                        break;
                    case 2:
                        if (!var7.trim().equals("")) {
                            var6 = new Integer(var7.trim());
                        }
                }
            }
            return new Color(var4, var5, var6);
        } else {
            return null;
        }
    }

    private static void setBorder(JSONArray borderInfoObjectList, XSSFWorkbook workbook, XSSFSheet sheet) {
        //设置边框样式map
        Map<Integer, BorderStyle> bordMap = new HashMap<>();
        bordMap.put(1, BorderStyle.THIN);
        bordMap.put(2, BorderStyle.HAIR);
        bordMap.put(3, BorderStyle.DOTTED);
        bordMap.put(4, BorderStyle.DASHED);
        bordMap.put(5, BorderStyle.DASH_DOT);
        bordMap.put(6, BorderStyle.DASH_DOT_DOT);
        bordMap.put(7, BorderStyle.DOUBLE);
        bordMap.put(8, BorderStyle.MEDIUM);
        bordMap.put(9, BorderStyle.MEDIUM_DASHED);
        bordMap.put(10, BorderStyle.MEDIUM_DASH_DOT);
        bordMap.put(11, BorderStyle.MEDIUM_DASH_DOT_DOT);
        bordMap.put(12, BorderStyle.SLANTED_DASH_DOT);
        bordMap.put(13, BorderStyle.THICK);

        //一定要通过 cell.getCellStyle()  不然的话之前设置的样式会丢失
        //设置边框
        if (borderInfoObjectList != null && borderInfoObjectList.size() > 0) {
            for (int i = 0; i < borderInfoObjectList.size(); i++) {
                JSONObject borderInfoObject = (JSONObject) borderInfoObjectList.get(i);
                if (borderInfoObject.get("rangeType").equals("cell")) {//单个单元格
                    JSONObject borderValueObject = borderInfoObject.getJSONObject("value");

                    JSONObject l = borderValueObject.getJSONObject("l");
                    JSONObject r = borderValueObject.getJSONObject("r");
                    JSONObject t = borderValueObject.getJSONObject("t");
                    JSONObject b = borderValueObject.getJSONObject("b");

                    int row = borderValueObject.getInt("row_index");
                    int col = borderValueObject.getInt("col_index");

                    XSSFCell cell = sheet.getRow(row).getCell(col);
                    if (l != null) {
                        cell.getCellStyle().setBorderLeft(bordMap.get(l.getInt("style"))); //左边框
                        String colorStr = l.getStr("color");
                        if(colorStr.indexOf("rgb") != -1){
                            cell.getCellStyle().setLeftBorderColor(new XSSFColor(getColor(colorStr)));//左边框颜色
                        }else {
                            int bg = Integer.parseInt(colorStr.replace("#", ""), 16);
                            cell.getCellStyle().setLeftBorderColor(new XSSFColor(new Color(bg)));//左边框颜色
                        }
                    }
                    if (r != null) {
                        cell.getCellStyle().setBorderRight(bordMap.get(r.getInt("style"))); //右边框
                        String colorStr = r.getStr("color");
                        if(colorStr.indexOf("rgb") != -1){
                            cell.getCellStyle().setRightBorderColor(new XSSFColor(getColor(colorStr)));//顶部边框颜色
                        }else {
                            int bg = Integer.parseInt(colorStr.replace("#", ""), 16);
                            cell.getCellStyle().setRightBorderColor(new XSSFColor(new Color(bg)));//右边框颜色
                        }
                    }
                    if (t != null) {
                        cell.getCellStyle().setBorderTop(bordMap.get(t.getInt("style"))); //顶部边框
                        String colorStr = t.getStr("color");
                        if(colorStr.indexOf("rgb") != -1){
                            cell.getCellStyle().setTopBorderColor(new XSSFColor(getColor(colorStr)));//顶部边框颜色
                        }else {
                            int bg = Integer.parseInt(colorStr.replace("#", ""), 16);
                            cell.getCellStyle().setTopBorderColor(new XSSFColor(new Color(bg)));//顶部边框颜色
                        }
                    }
                    if (b != null) {
                        cell.getCellStyle().setBorderBottom(bordMap.get(b.getInt("style"))); //底部边框
                        String colorStr = b.getStr("color");
                        if(colorStr.indexOf("rgb") != -1){
                            cell.getCellStyle().setBottomBorderColor(new XSSFColor(getColor(colorStr)));//顶部边框颜色
                        }else {
                            int bg = Integer.parseInt(colorStr.replace("#", ""), 16);
                            cell.getCellStyle().setBottomBorderColor(new XSSFColor(new Color(bg)));//底部边框颜色
                        }
                    }
                } else if (borderInfoObject.get("rangeType").equals("range")) {//选区
                    int bg_ = Integer.parseInt(borderInfoObject.getStr("color").replace("#", ""), 16);
                    int style_ = borderInfoObject.getInt("style");

                    JSONObject rangObject = (JSONObject) ((JSONArray) (borderInfoObject.get("range"))).get(0);
                    JSONArray rowList = rangObject.getJSONArray("row");
                    JSONArray columnList = rangObject.getJSONArray("column");
                    for (int row_ = rowList.getInt(0); row_ < rowList.getInt(rowList.size() - 1) + 1; row_++) {
                        for (int col_ = columnList.getInt(0); col_ < columnList.getInt(columnList.size() - 1) + 1; col_++) {
                            XSSFCell cell = sheet.getRow(row_).getCell(col_);
                            cell.getCellStyle().setBorderLeft(bordMap.get(style_)); //左边框
                            cell.getCellStyle().setLeftBorderColor(new XSSFColor(new Color(bg_)));//左边框颜色
                            cell.getCellStyle().setBorderRight(bordMap.get(style_)); //右边框
                            cell.getCellStyle().setRightBorderColor(new XSSFColor(new Color(bg_)));//右边框颜色
                            cell.getCellStyle().setBorderTop(bordMap.get(style_)); //顶部边框
                            cell.getCellStyle().setTopBorderColor(new XSSFColor(new Color(bg_)));//顶部边框颜色
                            cell.getCellStyle().setBorderBottom(bordMap.get(style_)); //底部边框
                            cell.getCellStyle().setBottomBorderColor(new XSSFColor(new Color(bg_)));//底部边框颜色 }
                        }
                    }
                }
            }
        }
    }

    /**
     * 設置值和樣式
     * @param jsonObjectList
     * @param borderInfoObjectList
     * @param sheet
     * @param workbook
     */
    private static void setCellValue(JSONArray jsonObjectList, JSONArray borderInfoObjectList, XSSFSheet sheet, XSSFWorkbook workbook) {
        //设置字体大小和颜色
        Map<Integer, String> fontMap = new HashMap<>();
        fontMap.put(-1, "Arial");
        fontMap.put(0, "Times New Roman");
        fontMap.put(1, "Arial");
        fontMap.put(2, "Tahoma");
        fontMap.put(3, "Verdana");
        fontMap.put(4, "微软雅黑");
        fontMap.put(5, "宋体");
        fontMap.put(6, "黑体");
        fontMap.put(7, "楷体");
        fontMap.put(8, "仿宋");
        fontMap.put(9, "新宋体");
        fontMap.put(10, "华文新魏");
        fontMap.put(11, "华文行楷");
        fontMap.put(12, "华文隶书");

        //遍歷每一個單元格（先遍歷行，再遍歷列）
        for (int index = 0; index < jsonObjectList.size(); index++) {
            XSSFCellStyle style = workbook.createCellStyle();//样式
            XSSFFont font = workbook.createFont();//字体样式
            //獲取單元格
            JSONObject object = jsonObjectList.getJSONObject(index);
            //str_ = 行坐標+列坐標=內容
            String str_ = object.getInt("r") + "_" + object.get("c") + "=" + ((JSONObject) object.get("v")).get("v") + "\n";
            JSONObject jsonObjectValue = ((JSONObject) object.get("v"));//獲取單元格樣式
            //單元格內容
            String value = "";
            if (jsonObjectValue != null && jsonObjectValue.get("v") != null) {
                value = jsonObjectValue.getStr("v");
            }

            if (sheet.getRow(object.getInt("r")) != null && sheet.getRow(object.getInt("r")).getCell(object.getInt("c")) != null) {
                XSSFCell cell = sheet.getRow(object.getInt("r")).getCell(object.getInt("c"));
                //設置公式
                if (jsonObjectValue != null && jsonObjectValue.get("f") != null) {//如果有公式，设置公式
                    value = jsonObjectValue.getStr("f");
                    cell.setCellFormula(value.substring(1,value.length()));//不需要=符号,例：INT(12.3)
                }
                //合并单元格与填充单元格颜色
                setMergeAndColorByObject(jsonObjectValue, sheet, style);
                //填充值
                cell.setCellValue(value);
                //XSSFRow row = sheet.getRow(object.getInt("r"));

                //设置垂直水平对齐方式
                int vt = jsonObjectValue.getInt("vt") == null ? 1 : jsonObjectValue.getInt("vt");//垂直对齐	 0 中间、1 上、2下
                int ht = jsonObjectValue.getInt("ht") == null ? 1 : jsonObjectValue.getInt("ht");//0 居中、1 左、2右
                switch (vt) {
                    case 0:
                        style.setVerticalAlignment(VerticalAlignment.CENTER);
                        break;
                    case 1:
                        style.setVerticalAlignment(VerticalAlignment.TOP);
                        break;
                    case 2:
                        style.setVerticalAlignment(VerticalAlignment.BOTTOM);
                        break;
                }
                switch (ht) {
                    case 0:
                        style.setAlignment(HorizontalAlignment.CENTER);
                        break;
                    case 1:
                        style.setAlignment(HorizontalAlignment.LEFT);
                        break;
                    case 2:
                        style.setAlignment(HorizontalAlignment.RIGHT);
                        break;
                }

                //设置合并单元格的样式有问题
                String ff = jsonObjectValue.getStr("ff");//0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体（Song）、6 黑体（ST Heiti）、7 楷体（ST Kaiti）、 8 仿宋（ST FangSong）、9 新宋体（ST Song）、10 华文新魏、11 华文行楷、12 华文隶书
                int fs = jsonObjectValue.getInt("fs") == null ? 14 : jsonObjectValue.getInt("fs");//字体大小
                int bl = jsonObjectValue.getInt("bl") == null ? 0 : jsonObjectValue.getInt("bl");//粗体	0 常规 、 1加粗
                int it = jsonObjectValue.getInt("it") == null ? 0 : jsonObjectValue.getInt("it");//斜体	0 常规 、 1 斜体
                String fc = jsonObjectValue.getStr("fc") == null ? "" : jsonObjectValue.getStr("fc");//字体颜色
//                font.setFontName(fontMap.get(ff));//字体名字
                //字體顏色
                if (fc.length() > 0) {
                    if(fc.indexOf("rgb") != -1){
                        font.setColor(new XSSFColor(getColor(fc)));
                    }else {
                        font.setColor(new XSSFColor(new Color(Integer.parseInt(fc.replace("#", ""), 16))));
                    }
                }
                font.setFontName(ff);//字体名字
                font.setFontHeightInPoints((short) fs);//字体大小
                //是否粗體
                if (bl == 1) {
                    font.setBold(true);//粗体显示
                }
                //是否斜體
                font.setItalic(it == 1 ? true : false);//斜体
                style.setFont(font);
                style.setWrapText(true);//设置自动换行
                cell.setCellStyle(style);
            } else {
                log.warn("错误的=" + index + ">>>" + str_);
            }
        }
        //设置边框
        setBorder(borderInfoObjectList, workbook, sheet);
    }
}

